package com.ocom.human.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ocom.common.entity.human.EntityRoommateInfo;
import com.ocom.common.vo.human.StudentInfoRVo;
//import com.ocom.human.vo.StudentInfoRVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface IDaoRoommateInfo extends BaseMapper<EntityRoommateInfo> {


    @Select({"<script>",
            "SELECT  region_orig_id,region_online,region_id,p_id,com_id,dept_id,p_code,p_name,p_sex,p_type,p_phone,p_identity,dept_name," +
                    "status,roomPerson,create_time,update_time FROM \n" +
                    "(select p.region_orig_id,p.region_online,p.region_id,p.p_id,p.com_id,p.dept_id,p.p_code,p.p_name,p.p_sex,p.p_type," +
                    "CONCAT(LEFT(p.p_phone,2),'********',RIGHT(p.p_phone,3)) as p_phone,d.dept_name,\n" +
                    "CONCAT(LEFT(p.p_identity,2),'********',RIGHT(p.p_identity,3)) as p_identity,p.status,count(r.p_id) as roomPerson,p.create_time,p.update_time  from personnel_info p \n" +
                    "left join roommate_info r on p.com_id=r.com_id and p.p_id=r.p_id \n" +
                    "left join dept_info d ON d.com_id=p.com_id and d.dept_id = p.dept_id  "+  //and p.p_id=r.p_id
                    "where p.com_id=#{comId} "+
                    " <when test=\"pId!=null and pullFailed &gt; 0\" > "+
                    " AND p.p_id=#{pId} "+
                    " </when> "+
                    " <when test=\"pName!=null and pName!=''\"> "+
                    " AND p.p_name like concat('%', #{pName}, '%') "+
                    " </when> "+
                    " <when test=\"pCode!=null and pCode!=''\" > "+
                    " AND p.p_code = #{pCode} "+
                    " </when> "+
                    " <when test=\"deptIdsList!=null and deptIdsList.size() &gt; 0 \" > "+
                    " AND p.dept_id  in " +
                    " <foreach collection='deptIdsList' item='id' open='(' separator=',' close=')'> "+
                    " #{id} "+
                    " </foreach> "+
                    "</when>"+
                    " <when test=\"encryIdentity!=null and encryIdentity!=''\" > "+
                    " AND p.encry_identity = #{encryIdentity}  "+
                    " </when> "+
                    " <when test=\"encryPhone!=null and encryPhone!=''\" > "+
                    " AND p.encry_phone = #{encryPhone}  "+
                    " </when> "+
                    " <when test=\"status!=null and status != ''\" > "+
                    " AND p.status = #{status}  "+
                    " </when> "+
                    " <when test=\"pType!=null and pType &gt; 0\" > "+
                    " AND p.p_type = #{pType}  "+
                    " </when> "+
                    " <when test=\"regionOrigId!=null and regionOrigId &gt; 0\" > "+
                    " AND p.region_orig_id=#{regionOrigId} "+
                    " </when> "+
                    " <when test=\"regionId!=null and regionId &gt; 0\" > "+
                    " AND p.region_id=#{regionId} "+
                    " </when> "+
                    " <when test=\"regionOnline!=null and regionOnline &gt; -1\" > "+
                    " AND p.region_online=#{regionOnline} "+
                    " </when> "+
                    " group by p.p_id) t \n" +
                    " <when test=\"roomPerson!=null and roomPerson &gt;= 0\" > "+
                    " where roomPerson = #{roomPerson}  "+
                    " AND status = 0  "+
                    " </when> "+
                    " order by t.com_id,t.dept_id,t.p_name,t.p_code "+
            "</script>"}
    )
    public List<StudentInfoRVo> PersonnelGetStudentInfo(Long comId, Long pId, List<Integer> deptIdsList, String encryIdentity, String encryPhone,
                                                        String pCode, String pName, Integer status, Long roomPerson, Integer pType, Long regionOrigId, Long regionId, Integer regionOnline);


    //导出手机号 不加密
    @Select({"<script>",
            "SELECT  region_orig_id,region_online,region_id,p_id,com_id,dept_id,p_code,p_name,p_sex,p_type,p_phone,p_identity,dept_name," +
                    "status,roomPerson,create_time,update_time,end_date FROM \n" +
                    "(select p.region_orig_id,p.region_online,p.region_id,p.p_id,p.com_id,p.dept_id,p.p_code,p.p_name,p.p_sex,p.p_type," +
                    "p_phone,d.dept_name,\n" +
                    "p_identity,p.status,count(r.p_id) as roomPerson,p.create_time,p.update_time,a.end_date  from personnel_info p \n" +
                    "left join roommate_info r on p.com_id=r.com_id and p.p_id=r.p_id \n" +
                    "left join xf_account_info as a on a.p_id = p.p_id and a.com_id = p.com_id "+
                    "left join dept_info d ON d.com_id=p.com_id and d.dept_id = p.dept_id  "+  //and p.p_id=r.p_id
                    "where p.com_id=#{comId} "+
                    " <when test=\"pId!=null and pullFailed &gt; 0\" > "+
                    " AND p.p_id=#{pId} "+
                    " </when> "+
                    " <when test=\"pName!=null and pName!=''\"> "+
                    " AND p.p_name like concat('%', #{pName}, '%') "+
                    " </when> "+
                    " <when test=\"pCode!=null and pCode!=''\" > "+
                    " AND p.p_code = #{pCode} "+
                    " </when> "+
                    " <when test=\"deptIdsList!=null and deptIdsList.size() &gt; 0 \" > "+
                    " AND p.dept_id  in " +
                    " <foreach collection='deptIdsList' item='id' open='(' separator=',' close=')'> "+
                    " #{id} "+
                    " </foreach> "+
                    "</when>"+
                    " <when test=\"encryIdentity!=null and encryIdentity!=''\" > "+
                    " AND p.p_identity = #{encryIdentity,typeHandler = com.ocom.common.entity.typehandler.PersonInfoHandle }" +
                    " </when> "+
                    " <when test=\"encryPhone!=null and encryPhone!=''\" > "+
                    " AND p.p_phone = #{encryPhone,typeHandler = com.ocom.common.entity.typehandler.PersonInfoHandle }" +
                    " </when> "+
                    " <when test=\"status!=null\" > "+
                    " AND p.status = #{status}  "+
                    " </when> "+
                    " <when test=\"pType!=null and pType &gt; 0\" > "+
                    " AND p.p_type = #{pType}  "+
                    " </when> "+
                    " <when test=\"regionOrigId!=null and regionOrigId &gt; 0\" > "+
                    " AND p.region_orig_id=#{regionOrigId} "+
                    " </when> "+
                    " <when test=\"regionId!=null and regionId &gt; 0\" > "+
                    " AND p.region_id=#{regionId} "+
                    " </when> "+
                    " <when test=\"regionOnline!=null and regionOnline &gt; -1\" > "+
                    " AND p.region_online=#{regionOnline} "+
                    " </when> "+
                    " group by p.p_id) t \n" +
                    " <when test=\"roomPerson!=null and roomPerson &gt;= 0\" > "+
                    " where roomPerson = #{roomPerson}  "+
                    " AND status = 0  "+
                    " </when> "+
                    " order by t.com_id,t.dept_id,t.p_name,t.p_code "+
                    "</script>"}
    )
    public List<StudentInfoRVo> PersonnelGetStudentInfo1(Long comId, Long pId, List<Integer> deptIdsList, String encryIdentity, String encryPhone,
                                                        String pCode, String pName, Integer status, Long roomPerson, Integer pType, Long regionOrigId, Long regionId, Integer regionOnline);
}
